
IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE id = object_id(N'dbo.[CustomerOrders]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE dbo.[Order] DROP CONSTRAINT [CustomerOrders]
GO

IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE id = object_id(N'dbo.[OrderProducts]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE dbo.[OrderProduct] DROP CONSTRAINT [OrderProducts]
GO

IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE id = object_id(N'dbo.[ProductOrders]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE dbo.[OrderProduct] DROP CONSTRAINT [ProductOrders]
GO

IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE id = object_id(N'dbo.[Customer]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.[Customer]
GO

IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE id = object_id(N'dbo.[Order]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.[Order]
GO

IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE id = object_id(N'dbo.[OrderProduct]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.[OrderProduct]
GO

IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE id = object_id(N'dbo.[Product]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.[Product]
GO

CREATE TABLE dbo.[Customer] (
	[CustomerId] int IDENTITY ( 1,1 ) NOT NULL,
	[Firstname] varchar(50) NULL,
	[Lastname] varchar(50) NULL
)
GO

ALTER TABLE dbo.[Customer] WITH NOCHECK ADD 
	CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
	(
		[CustomerId]
	)  ON [PRIMARY]
GO

CREATE TABLE dbo.[Order] (
	[OrderId] int IDENTITY ( 1,1 ) NOT NULL,
	[OrderDate] datetime DEFAULT ((getdate())) NOT NULL,
	[Customer] int NOT NULL
)
GO

ALTER TABLE dbo.[Order] WITH NOCHECK ADD 
	CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
	(
		[OrderId]
	)  ON [PRIMARY]
GO

CREATE TABLE dbo.[OrderProduct] (
	[Product] int NOT NULL,
	[Order] int NOT NULL
)
GO

ALTER TABLE dbo.[OrderProduct] WITH NOCHECK ADD 
	CONSTRAINT [PK_OrderProduct] PRIMARY KEY CLUSTERED
	(
		[Product],
		[Order]
	)  ON [PRIMARY]
GO

CREATE TABLE dbo.[Product] (
	[ProductId] int IDENTITY ( 1,1 ) NOT NULL,
	[Name] varchar(50) NOT NULL,
	[Cost] decimal(18,2) NOT NULL
)
GO

ALTER TABLE dbo.[Product] WITH NOCHECK ADD 
	CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
	(
		[ProductId]
	)  ON [PRIMARY]
GO

ALTER TABLE dbo.[Order] ADD
	CONSTRAINT [CustomerOrders] FOREIGN KEY 
	(
		[Customer]
	) REFERENCES dbo.[Customer] (
		[CustomerId]
	) 
GO

ALTER TABLE dbo.[OrderProduct] ADD
	CONSTRAINT [OrderProducts] FOREIGN KEY 
	(
		[Order]
	) REFERENCES dbo.[Order] (
		[OrderId]
	) 
GO

ALTER TABLE dbo.[OrderProduct] ADD
	CONSTRAINT [ProductOrders] FOREIGN KEY 
	(
		[Product]
	) REFERENCES dbo.[Product] (
		[ProductId]
	) 
GO
